package com.audi.box.adapter;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.audi.dto.DeviceItem;
import com.audi.dto.SmartTriggerItem;
import com.audi.dto.SmartTriggerTriggerItem;
import com.audi.dto.SmartTriggerWhenItem;
import com.audi.dto.SmartTriggerWhenTriggerItem;

/**
 * 
 * @author HuyPVA
 * 
 */
public class SQLiteAsusHelperSmartTrigger extends SQLiteAsusHelperCommon {
    
    /**
     * @param context
     */
    public SQLiteAsusHelperSmartTrigger(Context context) {
        super(context);
    }
    
    // insert table smart_trigger Namnt16 ver1 test
    // please does not delete rela method
    public int sequentialInsertSmartTrigger(SmartTriggerItem item) {
        /*
         * Log.d("HuySQLite Log", "sequentialInsertSmartTrigger"); int id = -1;
         * int whenid, triggerid; SmartTriggerItem smartitem = item; whenid =
         * addWhentable(smartitem); triggerid = addTriggertable(smartitem); if
         * (whenid > 0 && triggerid > 0) { smartitem.setWhenId(whenid);
         * smartitem.setTriggerId(triggerid); id =
         * insertTriggerWhentable(smartitem); } return id;
         */
        return -1;
    }
    
    /**
     * Insert When Table
     * 
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #addTriggerWhen(SmartTriggerWhenItem)}
     * @see #addTriggerWhen(SmartTriggerWhenItem)
     */
    @Deprecated
    public int insertWhentable(SmartTriggerItem item) {
        return 0;
    }
    
    /**
     * Add When Table
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem
     * @return <code>When id</code> if (successful, <code>0</code> if (When is
     *         exist
     * @see #addTriggerTrigger(SmartTriggerTriggerItem)
     * @see #addTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    public int addTriggerWhen(SmartTriggerWhenItem item) {
        Log.d("HuySQLite Log", "insertWhentable");
        ContentValues values = new ContentValues();
        values.put(KEY_ST_WHEN_DEVICE_ID, item.getWhenDeviceId());
        values.put(KEY_ST_WHEN_VALUE, item.getWhenDeviceValues());
        values.put(KEY_ST_WHEN_TYPE, item.getWhenDeviceType());
        // values.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, a5);
        
        return sqlInsert(TABLE_ST_WHEN, null, values);
    }
    
    /**
     * Insert Trigger Table
     * 
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #addTriggerTrigger(SmartTriggerTriggerItem)}
     * @see #addTriggerTrigger(SmartTriggerTriggerItem)
     */
    @Deprecated
    public int insertTriggertable(SmartTriggerItem item) {
        return 0;
    }
    
    /**
     * Add Trigger Table
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem
     * @return <code>Trigger id</code> if (successful, <code>0</code> if
     *         (Trigger is exist
     * @see #addTriggerWhen(SmartTriggerWhenItem)
     * @see #addTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    public int addTriggerTrigger(SmartTriggerTriggerItem item) {
        Log.d("HuySQLite Log", "insertTriggertable");
        ContentValues values = new ContentValues();
        values.put(KEY_ST_TRIGGER_DEVICE_ID, item.getTriggerDeviceId());
        values.put(KEY_ST_TRIGGER_VALUE, item.getTriggerDeviceValues());
        values.put(KEY_ST_TRIGGER_ACTION, item.getTriggerDeviceType());
        // values.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, a5);
        
        return sqlInsert(TABLE_ST_TRIGGER, null, values);
    }
    
    /**
     * Insert Trigger-When Table
     * 
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #addTriggerWhenTrigger(SmartTriggerWhenTriggerItem)}
     * @see #addTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    @Deprecated
    public int insertTriggerWhentable(SmartTriggerItem item) {
        return 0;
    }
    
    /**
     * Add Trigger-When Table
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem
     * @return <code>Trigger-When id</code> if (successful, <code>0</code> if
     *         (device is exist
     * @see #addTriggerWhen(SmartTriggerWhenItem)
     * @see #addTriggerTrigger(SmartTriggerTriggerItem)
     */
    public int addTriggerWhenTrigger(SmartTriggerWhenTriggerItem item) {
        Log.d("HuySQLite Log", "insertTriggerWhentable");
        ContentValues values = new ContentValues();
        values.put(KEY_ST_WHEN_TRIGGER_WHEN_ID, item.getWhenId());
        values.put(KEY_ST_WHEN_TRIGGER_TRIGGER_ID, item.getTriggerId());
        values.put(KEY_ST_WHEN_TRIGGER_NAME, item.getWhenTriggerName());
        // values.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, a5);
        
        return sqlInsert(TABLE_ST_WHEN_TRIGGER, null, values);
    }
    
    // get device id form table when NamNT16
    /**
     * Get Device value of Smart Trigger When
     * 
     * @author NamNT16
     * @deprecated {@link #getDeviceValueFromSTWhen(SmartTriggerWhenItem)}
     * @param item
     *            SmartTriggerItem
     * @return Device value
     * @see #getDeviceValueFromSTWhen(SmartTriggerWhenItem)
     */
    @Deprecated
    public int getwhenValformWhen(SmartTriggerItem item) {
        return -1;
    }
    
    /**
     * Get Device ID of Smart Trigger When
     * 
     * @author NamNT16
     * @param trigger
     *            SmartTriggerWhenItem
     * @return Device id if (successful, -1 if (Smart Trigger does not exist
     * @see #getDeviceIdFromSTTrigger(SmartTriggerTriggerItem)
     */
    public int getDeviceIdFromSTWhen(SmartTriggerWhenItem trigger) {
        Log.d("HuySQLite Log", "getdeviceIdformWhen");
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_WHEN_DEVICE_ID + " FROM "
                + TABLE_ST_WHEN + " WHERE " + KEY_ST_WHEN_ID + "="
                + trigger.getWhenId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if ((cursorSqliteAsusHelper.getCount() > 0)
                && (cursorSqliteAsusHelper.moveToFirst())) {
            return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
        }
        
        return i;
    }
    
    /**
     * Get Device ID of Smart Trigger Trigger
     * 
     * @author NamNT16
     * @param trigger
     *            SmartTriggerItem
     * @return Device id if (successful, -1 if (Smart Trigger does not exist
     * @see #getDeviceIdFromSTWhen(SmartTriggerWhenItem)
     */
    public int getDeviceIdFromSTTrigger(SmartTriggerTriggerItem trigger) {
        Log.d("HuySQLite Log", "getdeviceIdformTrigger");
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_TRIGGER_DEVICE_ID + " FROM "
                + TABLE_ST_TRIGGER + " WHERE " + KEY_ST_TRIGGER_ID + "="
                + trigger.getTriggerId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if ((cursorSqliteAsusHelper.getCount() > 0)
                && (cursorSqliteAsusHelper.moveToFirst())) {
            return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
        }
        if (db != null) {
            db.close();
        }
        return i;
    }
    
    /**
     * Get Device value of Smart Trigger When
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem
     * @return Device value, -1 if (Smart Trigger does not exist
     * @see #getDeviceValueFromSTWhen(SmartTriggerWhenItem)
     */
    public int getDeviceValueFromSTWhen(SmartTriggerWhenItem trigger) {
        Log.d("HuySQLite Log", "getDeviceValueFromSTWhen");
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_WHEN_VALUE + " FROM "
                + TABLE_ST_WHEN + " WHERE " + KEY_ST_WHEN_ID + "="
                + trigger.getWhenId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if ((cursorSqliteAsusHelper.getCount() > 0)
                && (cursorSqliteAsusHelper.moveToFirst())) {
            return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
        }
        if (db != null) {
            db.close();
        }
        return i;
    }
    
    /**
     * Get Device value of Smart Trigger Trigger
     * 
     * @author NamNT16
     * @deprecated {@link #getDeviceValueFromSTTrigger(SmartTriggerTriggerItem)}
     * @param item
     *            SmartTriggerItem
     * @return Device value
     * @see #getDeviceValueFromSTTrigger(SmartTriggerTriggerItem)
     */
    // get device id form table when NamNT16
    @Deprecated
    public int gettriggerValformTrigger(SmartTriggerItem item) {
        return -1;
    }
    
    /**
     * Get Device value of Smart Trigger Trigger
     * 
     * @author CuongPH7
     * @param trigger
     *            SmartTriggerTriggerItem
     * @return Device value
     * @see #getDeviceValueFromSTTrigger(SmartTriggerTriggerItem)
     */
    public int getDeviceValueFromSTTrigger(SmartTriggerTriggerItem trigger) {
        Log.d("HuySQLite Log", "gettriggerValformTrigger");
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_TRIGGER_VALUE + " FROM "
                + TABLE_ST_TRIGGER + " WHERE " + KEY_ST_TRIGGER_ID + "="
                + trigger.getTriggerId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0
                && (cursorSqliteAsusHelper.moveToFirst())) {
            return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
        }
        if (db != null) {
            db.close();
        }
        return i;
        
    }
    
    /**
     * Get all When Trigger List
     * 
     * @author NamNT16
     * @deprecated {@link #getSmartTrigerWhenTriggerList()}
     * @return ArrayList SmartTriggerItem
     * @see #getSmartTrigerWhenTriggerList()
     */
    @Deprecated
    public ArrayList<SmartTriggerItem> getsmartTriggerList() {
        return null;
    }
    
    /**
     * Get all When Trigger List
     * 
     * @author CuongPH7
     * @return ArrayList SmartTriggerWhenTriggerItem
     * @see SmartTriggerWhenTriggerItem
     */
    public ArrayList<SmartTriggerWhenTriggerItem> getSmartTrigerWhenTriggerList() {
        Log.d("HuySQLite Log", "getsmartTriggerList");
        ArrayList<SmartTriggerWhenTriggerItem> arrSmartTriggerItems = new ArrayList<SmartTriggerWhenTriggerItem>();
        
        // SmartTriggerItem smartTriggerItem=new SmartTriggerItem();
        String selectQuery = "SELECT * FROM " + TABLE_ST_WHEN_TRIGGER
                + " WHERE " + KEY_ST_WHEN_TRIGGER_IS_DELETED + " != 1";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            
            if (cursor.moveToFirst()) {
                do {
                    SmartTriggerWhenTriggerItem smartTriggerItem = new SmartTriggerWhenTriggerItem();
                    // smartTriggerItem.setSmartTriggerID(cursor.getInt(0));
                    smartTriggerItem.setWhenId(cursor.getInt(0));
                    smartTriggerItem.setTriggerId(cursor.getInt(1));
                    smartTriggerItem.setWhenTriggerName(cursor.getString(2));
                    smartTriggerItem.setOnOff(cursor.getInt(4));
                    arrSmartTriggerItems.add(smartTriggerItem);
                } while (cursor.moveToNext());
                
            }
            // db.close();
            
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return arrSmartTriggerItems;
    }
    
    /**
     * Get Smart Trigger Item (SmartTriggerItem)
     * 
     * @author CuongPH7
     * @return SmartTriggerItem
     * @see #SmartTriggerItem
     */
    public SmartTriggerItem getSmartTriger(int stWhenId, int stTriggerId) {
        Log.d("HuySQLite Log", "getSmartTriger");
        
        SmartTriggerItem smartTriggerItem = new SmartTriggerItem();
        
        ArrayList<SmartTriggerItem> mLst = getListSmartTrigger(stWhenId,
                stTriggerId);
        if (mLst != null && mLst.size() > 0) {
            smartTriggerItem = mLst.get(0);
        }
        
        return smartTriggerItem;
    }
    
    /**
     * Set On/Off Smart Trigger
     * 
     * @author NAMNT16
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #setOnOffSmartTrigger(SmartTriggerWhenTriggerItem)}
     * @see #setOnOffSmartTrigger(SmartTriggerWhenTriggerItem)
     */
    @Deprecated
    public int onoffSmartTrigger(SmartTriggerItem item) {
        return 0;
    }
    
    /**
     * Set On/Off Smart Trigger
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerWhenTriggerItem return <code>1</code> if
     *            (successful, 0 if (SmartTrigger When does not exist
     */
    public int setOnOffSmartTrigger(SmartTriggerWhenTriggerItem item) {
        Log.d("HuySQLite Log", "getOnOffSmartTrigger");
        
        ContentValues values = new ContentValues();
        values.put(KEY_ST_WHEN_TRIGGER_ONOFF, item.getOnOff());
        
        return sqlUpdate(
                TABLE_ST_WHEN_TRIGGER,
                values,
                KEY_ST_WHEN_TRIGGER_WHEN_ID + "="
                        + String.valueOf(item.getWhenId()), null);
    }
    
    /**
     * Update SmartTrigger When
     * 
     * @author NAMNT16
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #updateSmartTriggerWhen(SmartTriggerWhenItem)}
     * @see #updateSmartTriggerWhen(SmartTriggerWhenItem)
     * @see #updateSmartTriggerTrigger(SmartTriggerTriggerItem)
     * @see #updateSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    @Deprecated
    public void update1(SmartTriggerItem item) {
        
    }
    
    /**
     * Update SmartTrigger Trigger
     * 
     * @author NAMNT16
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #updateSmartTriggerTrigger(SmartTriggerTriggerItem)}
     * @see #updateSmartTriggerWhen(SmartTriggerWhenItem)
     * @see #updateSmartTriggerTrigger(SmartTriggerTriggerItem)
     * @see #updateSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    @Deprecated
    public void update2(SmartTriggerItem item) {
        
    }
    
    /**
     * Update SmartTrigger When-Trigger
     * 
     * @author NAMNT16
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #updateSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem)}
     * @see #updateSmartTriggerWhen(SmartTriggerWhenItem)
     * @see #updateSmartTriggerTrigger(SmartTriggerTriggerItem)
     * @see #updateSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    @Deprecated
    public void updateSmartTrigger(SmartTriggerItem item) {
        
    };
    
    /**
     * Update SmartTrigger When
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem return <code>1</code> if (successful, 0 if
     *            (SmartTrigger When does not exist
     */
    public int updateSmartTriggerWhen(SmartTriggerWhenItem item) {
        Log.d("HuySQLite Log", "updateSmartTrigger");
        ContentValues valuesWhen = new ContentValues();
        valuesWhen.put(KEY_ST_WHEN_DEVICE_ID, item.getWhenDeviceId());
        valuesWhen.put(KEY_ST_WHEN_VALUE, item.getWhenDeviceValues());
        valuesWhen.put(KEY_ST_WHEN_TYPE, item.getWhenDeviceType());
        
        return sqlUpdate(TABLE_ST_WHEN, valuesWhen,
                KEY_ST_WHEN_ID + "=" + item.getWhenId(), null);
    }
    
    /**
     * Update SmartTrigger Trigger
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem return <code>1</code> if (successful, 0 if
     *            (SmartTrigger Trigger does not exist
     */
    public int updateSmartTriggerTrigger(SmartTriggerTriggerItem item) {
        Log.d("HuySQLite Log", "updateSmartTrigger");
        ContentValues valuesWhen = new ContentValues();
        valuesWhen.put(KEY_ST_TRIGGER_DEVICE_ID, item.getTriggerDeviceId());
        valuesWhen.put(KEY_ST_TRIGGER_VALUE, item.getTriggerDeviceValues());
        valuesWhen.put(KEY_ST_TRIGGER_ACTION, item.getTriggerDeviceType());
        
        return sqlUpdate(TABLE_ST_TRIGGER, valuesWhen, KEY_ST_TRIGGER_ID + "="
                + item.getTriggerId(), null);
    }
    
    /**
     * Update SmartTrigger When-Trigger
     * 
     * @author CuongPH7
     * @param item
     *            SmartTriggerItem return <code>1</code> if (successful, 0 if
     *            (SmartTrigger When-Trigger does not exist
     */
    public int updateSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem item) {
        Log.d("HuySQLite Log", "updateSmartTrigger");
        ContentValues valuesWhen = new ContentValues();
        valuesWhen.put(KEY_ST_WHEN_TRIGGER_NAME, item.getWhenTriggerName());
        valuesWhen.put(KEY_ST_WHEN_TRIGGER_ONOFF, item.getOnOff());
        
        return sqlUpdate(TABLE_ST_WHEN_TRIGGER, valuesWhen,
                KEY_ST_WHEN_TRIGGER_WHEN_ID + "=" + item.getWhenId(), null);
    }
    
    /**
     * Delete SmartTrigger When-Trigger
     * 
     * @author NAMNT16
     * @param item
     *            SmartTriggerItem
     * @deprecated {@link #deleleSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem)}
     * @see #deleleSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem)
     */
    @Deprecated
    public void delSmartTrigger(SmartTriggerItem item) {
        
    }
    
    /**
     * Delete SmartTrigger When
     * 
     * @param item
     *            SmartTriggerItem return <code>1</code> if (successful, 0 if
     *            (SmartTrigger When does not exist
     */
    public int deleleSmartTriggerWhen(SmartTriggerWhenItem item) {
        Log.d("HuySQLite Log", "delSmartTrigger");
        ContentValues val = new ContentValues();
        val.put(KEY_ST_WHEN_IS_DELETED, 1);
        
        return sqlUpdate(TABLE_ST_WHEN, val,
                KEY_ST_WHEN_ID + "=" + item.getWhenId(), null);
    }
    
    /**
     * Delete SmartTrigger Trigger
     * 
     * @param item
     *            SmartTriggerItem return <code>1</code> if (successful, 0 if
     *            (SmartTrigger Trigger does not exist
     */
    public int deleleSmartTriggerTrigger(SmartTriggerTriggerItem item) {
        Log.d("HuySQLite Log", "deleleSmartTriggerTrigger");
        ContentValues val = new ContentValues();
        val.put(KEY_ST_TRIGGER_IS_DELETED, 1);
        
        return sqlUpdate(TABLE_ST_TRIGGER, val,
                KEY_ST_TRIGGER_ID + "=" + item.getTriggerId(), null);
    }
    
    /**
     * Delete SmartTrigger When-Trigger
     * 
     * @param item
     *            SmartTriggerItem return <code>1</code> if (successful, 0 if
     *            (SmartTrigger When-Trigger does not exist
     */
    public int deleleSmartTriggerWhenTrigger(SmartTriggerWhenTriggerItem item) {
        Log.d("HuySQLite Log", "deleleSmartTriggerWhenTrigger");
        ContentValues val = new ContentValues();
        val.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, 1);
        
        return sqlUpdate(TABLE_ST_WHEN_TRIGGER, val,
                KEY_ST_WHEN_TRIGGER_WHEN_ID + "=" + item.getWhenId(), null);
    }
    
    /**
     * Get Smart Trigger By Device
     * 
     * @deprecated {@link #getListSmartTrigger()}
     */
    @Deprecated
    public ArrayList<SmartTriggerItem> getListSmartTrigger(DeviceItem device) {
        Log.d("HuySQLite Log", "getListSmartTrigger");
        /*
         * ArrayList<SmartTriggerItem> mLst = new ArrayList<SmartTriggerItem>();
         * String sqlSelect = "SELECT " + TABLE_ST_WHEN + "." +
         * KEY_ST_WHEN_VALUE + ", " + TABLE_ST_WHEN + "." + KEY_ST_WHEN_TYPE +
         * ", " + TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_VALUE + ", " +
         * TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_ACTION + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_NAME + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_WHEN_ID + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + ", " +
         * TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_DEVICE_ID + ", " +
         * TABLE_ST_WHEN + "." + KEY_ST_WHEN_DEVICE_ID + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_ONOFF + " FROM " +
         * TABLE_ST_WHEN + ", " + TABLE_ST_TRIGGER + ", " +
         * TABLE_ST_WHEN_TRIGGER + " WHERE " + TABLE_ST_WHEN_TRIGGER + "." +
         * KEY_ST_WHEN_TRIGGER_WHEN_ID + "=" + TABLE_ST_WHEN + "." +
         * KEY_ST_WHEN_ID + " AND " + TABLE_ST_WHEN_TRIGGER + "." +
         * KEY_ST_WHEN_TRIGGER_TRIGGER_ID + "=" + TABLE_ST_TRIGGER + "." +
         * KEY_ST_TRIGGER_ID + " AND " + "(" + TABLE_ST_TRIGGER + "." +
         * KEY_ST_TRIGGER_DEVICE_ID + "=" + device.getDeviceId() + " OR " +
         * TABLE_ST_WHEN + "." + KEY_ST_WHEN_DEVICE_ID + "=" +
         * device.getDeviceId() + ")" + " AND " + KEY_ST_WHEN_TRIGGER_IS_DELETED
         * + " ='0'"; SQLiteDatabase db = this.getReadableDatabase(); Cursor
         * cursor = db.rawQuery(sqlSelect, null); try { if
         * (cursor.moveToFirst()) { do { SmartTriggerWhenItem when = new
         * SmartTriggerWhenItem(); SmartTriggerTriggerItem trigger = new
         * SmartTriggerTriggerItem(); SmartTriggerWhenTriggerItem whenTrigger =
         * new SmartTriggerWhenTriggerItem();
         * 
         * //item.setWhenDeviceValues(cursor.getString(0));
         * when.setWhenDeviceValues(cursor.getString(0));
         * //item.setWhenDeviceType(cursor.getInt(1));
         * when.setWhenDeviceType(cursor.getInt(1));
         * //item.setTriggerDeviceValues(cursor.getString(2));
         * trigger.setTriggerDeviceValues(cursor.getString(2));
         * //item.setTriggerDeviceType(cursor.getInt(3));
         * trigger.setTriggerDeviceType(cursor.getInt(3));
         * //item.setSmarttriggername(cursor.getString(4));
         * whenTrigger.setWhenTriggerName(cursor.getString(4));
         * //item.setWhenId(cursor.getInt(5)); when.setWhenId(cursor.getInt(5));
         * //item.setTriggerId(cursor.getInt(6));
         * trigger.setTriggerId(cursor.getInt(6)); //item.setTriggerDeviceId(7);
         * trigger.setTriggerDeviceId(cursor.getInt(7));
         * //item.setWhenDeviceId(8); when.setWhenDeviceId(cursor.getInt(8));
         * //item.setIconControl(cursor.getInt(9));
         * whenTrigger.setOnOff(cursor.getInt(9)); SmartTriggerItem item = new
         * SmartTriggerItem(when, trigger, whenTrigger); mLst.add(item); } while
         * (cursor.moveToNext()); } return mLst;
         * 
         * } finally { if (cursor != null) { cursor.close(); } if (db != null) {
         * db.close(); } }
         */
        return null;
    }
    
    /**
     * Get All Smart Trigger List
     * 
     * @author CuongPH7 return An ArrayList SmartTriggerItem
     */
    public ArrayList<SmartTriggerItem> getListSmartTrigger() {
        return getListSmartTrigger(0, 0);
    }
    
    /**
     * Get Smart Trigger List By When Id and Trigger Id
     * 
     * @author unknown, modify by CuongPH7
     * @return ArrayList SmartTriggerItem
     */
    public ArrayList<SmartTriggerItem> getListSmartTrigger(int whenId,
            int triggerId) {
        Log.d("HuySQLite Log", "getListSmartTrigger2");
        ArrayList<SmartTriggerItem> mLst = new ArrayList<SmartTriggerItem>();
        /*
         * String sqlSelect = "SELECT " + TABLE_ST_WHEN + "." +
         * KEY_ST_WHEN_VALUE + ", " + TABLE_ST_WHEN + "." + KEY_ST_WHEN_TYPE +
         * ", " + TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_VALUE + ", " +
         * TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_ACTION + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_NAME + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_WHEN_ID + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + ", " +
         * TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_DEVICE_ID + ", " +
         * TABLE_ST_WHEN + "." + KEY_ST_WHEN_DEVICE_ID + ", " +
         * TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_ONOFF + " FROM " +
         * TABLE_ST_WHEN + ", " + TABLE_ST_TRIGGER + ", " +
         * TABLE_ST_WHEN_TRIGGER + " WHERE " + TABLE_ST_WHEN_TRIGGER + "." +
         * KEY_ST_WHEN_TRIGGER_WHEN_ID + "=" + TABLE_ST_WHEN + "." +
         * KEY_ST_WHEN_ID + " AND " + KEY_ST_WHEN_TRIGGER_IS_DELETED + " ='0'" +
         * " GROUP BY " + TABLE_ST_WHEN_TRIGGER + "." +
         * KEY_ST_WHEN_TRIGGER_TRIGGER_ID;
         */
        
        String selectQuery = "SELECT "
                // GET ST WHEN-TRIGGER INFOR
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_WHEN_ID
                + ", "
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_TRIGGER_ID
                + ", "
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_NAME
                + ", "
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_ONOFF
                + ", "
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_NOTI
                + ", "
                
                // GET ST WHEN INFOR
                + TABLE_ST_WHEN
                + "."
                + KEY_ST_WHEN_DEVICE_ID
                + ", "
                + "TABLE_DEVICE_A."
                + KEY_DEVICE_NAME
                + " AS WHEN_DEVICE_NAME"
                + ", "
                + "TABLE_DEVICE_A."
                + KEY_DEVICE_PLACE_ID
                + " AS WHEN_DEVICE_PLACE_ID"
                + ", "
                + "TABLE_PLACE_A."
                + KEY_PLACE_NAME
                + " AS WHEN_DEVICE_PLACE_NAME"
                + ", "
                + TABLE_ST_WHEN
                + "."
                + KEY_ST_WHEN_VALUE
                + ", "
                + TABLE_ST_WHEN
                + "."
                + KEY_ST_WHEN_TYPE
                + ", "
                
                // GET ST TRIGGER INFOR
                + TABLE_ST_TRIGGER
                + "."
                + KEY_ST_TRIGGER_DEVICE_ID
                + ", "
                + "TABLE_DEVICE_B."
                + KEY_DEVICE_NAME
                + " AS TRIGGER_DEVICE_NAME"
                + ", "
                + "TABLE_DEVICE_B."
                + KEY_DEVICE_PLACE_ID
                + " AS TRIGGER_DEVICE_PLACE_ID"
                + ", "
                + "TABLE_PLACE_B."
                + KEY_PLACE_NAME
                + " AS TRIGGER_DEVICE_PLACE_NAME"
                + ", "
                + TABLE_ST_TRIGGER
                + "."
                + KEY_ST_TRIGGER_VALUE
                + ", "
                + TABLE_ST_TRIGGER
                + "."
                + KEY_ST_TRIGGER_ACTION
                
                // WHEN-TRIGGER TABLE LEFT JOIN WHEN TABLE
                + " FROM "
                + TABLE_ST_WHEN_TRIGGER
                + " LEFT JOIN "
                + TABLE_ST_WHEN
                + " ON "
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_WHEN_ID
                + " = "
                + TABLE_ST_WHEN
                + "."
                + KEY_ST_WHEN_ID
                
                // WHEN TABLE LEFT JOIN DEVICE TABLE FOR GETTING WHEN DEVICE
                // INFORMATION
                + " LEFT JOIN "
                + TABLE_DEVICE
                + " AS TABLE_DEVICE_A"
                + " ON "
                + TABLE_ST_WHEN
                + "."
                + KEY_ST_WHEN_DEVICE_ID
                + " = TABLE_DEVICE_A."
                + KEY_DEVICE_ID
                
                // DEVICE TABLE LEFT JOIN PLACE TABLE FOR GETTING WHEN PALCE
                // INFORMATION
                + " LEFT JOIN "
                + TABLE_PLACE
                + " AS TABLE_PLACE_A"
                + " ON TABLE_DEVICE_A."
                + KEY_DEVICE_PLACE_ID
                + " = TABLE_PLACE_A."
                + KEY_PLACE_ID
                
                // WHEN-TRIGGER TABLE LEFT JOIN TRIGGER TABLE
                + " LEFT JOIN "
                + TABLE_ST_TRIGGER
                + " ON "
                + TABLE_ST_WHEN_TRIGGER
                + "."
                + KEY_ST_WHEN_TRIGGER_TRIGGER_ID
                + " = "
                + TABLE_ST_TRIGGER
                + "."
                + KEY_ST_TRIGGER_ID
                
                // WHEN TABLE LEFT JOIN DEVICE TABLE FOR GETTING TRIGGER DEVICE
                // INFORMATION
                + " LEFT JOIN " + TABLE_DEVICE + " AS TABLE_DEVICE_B" + " ON "
                + TABLE_ST_TRIGGER
                + "."
                + KEY_ST_TRIGGER_DEVICE_ID
                + " = TABLE_DEVICE_B."
                + KEY_DEVICE_ID
                
                // DEVICE TABLE LEFT JOIN PLACE TABLE FOR GETTING TRIGGER PALCE
                // INFORMATION
                + " LEFT JOIN " + TABLE_PLACE + " AS TABLE_PLACE_B"
                + " ON TABLE_DEVICE_B." + KEY_DEVICE_PLACE_ID
                + " = TABLE_PLACE_B." + KEY_PLACE_ID;
        
        ArrayList<String> where = new ArrayList<String>();
        
        if (whenId != 0) {
            where.add(TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_WHEN_ID
                    + "=" + whenId);
        }
        
        if (triggerId != 0) {
            where.add(TABLE_ST_WHEN_TRIGGER + "."
                    + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + "=" + triggerId);
        }
        
        where.add(TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_IS_DELETED
                + "=0");
        where.add(TABLE_ST_WHEN + "." + KEY_ST_WHEN_IS_DELETED + "=0");
        where.add(TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_IS_DELETED + "=0");
        
        selectQuery += " WHERE " + TextUtils.join(" AND ", where);
        
        selectQuery += " ORDER BY " + TABLE_ST_WHEN_TRIGGER + "."
                + KEY_ST_WHEN_TRIGGER_WHEN_ID + " DESC";
        
        Log.e("DATABASE ERROR", selectQuery);
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    SmartTriggerWhenItem when = new SmartTriggerWhenItem();
                    SmartTriggerTriggerItem trigger = new SmartTriggerTriggerItem();
                    SmartTriggerWhenTriggerItem whenTrigger = new SmartTriggerWhenTriggerItem();
                    
                    // SET WHEN-TRIGGER INFORMATION
                    whenTrigger.setWhenId(cursor.getInt(0));
                    whenTrigger.setTriggerId(cursor.getInt(1));
                    whenTrigger.setWhenTriggerName(cursor.getString(2));
                    whenTrigger.setOnOff(cursor.getInt(3));
                    whenTrigger.setNoti(cursor.getInt(4));
                    
                    // SET WHEN INFORMATION
                    when.setWhenId(cursor.getInt(0));
                    when.setWhenDeviceId(cursor.getInt(5));
                    when.setWhenDeviceName(cursor.getString(6));
                    when.setWhenDevicePlaceId(cursor.getInt(7));
                    when.setWhenDevicePlaceName(cursor.getString(8));
                    when.setWhenDeviceValues(cursor.getString(9));
                    when.setWhenDeviceType(cursor.getInt(10));
                    
                    // SET TRIGGER INFORMATION
                    trigger.setTriggerId(cursor.getInt(1));
                    trigger.setTriggerDeviceId(cursor.getInt(11));
                    trigger.setTriggerDeviceName(cursor.getString(12));
                    trigger.setTriggerDevicePlaceId(cursor.getInt(13));
                    trigger.setTriggerDevicePlaceName(cursor.getString(14));
                    trigger.setTriggerDeviceValues(cursor.getString(15));
                    trigger.setTriggerDeviceType(cursor.getInt(16));
                    
                    SmartTriggerItem item = new SmartTriggerItem(when, trigger,
                            whenTrigger);
                    mLst.add(item);
                    
                    /*
                     * SmartTriggerItem item = new SmartTriggerItem();
                     * item.setWhenDeviceValues(cursor.getString(0));
                     * item.setWhenDeviceType(cursor.getInt(1));
                     * item.setTriggerDeviceValues(cursor.getString(2));
                     * item.setTriggerDeviceType(cursor.getInt(3));
                     * item.setSmarttriggername(cursor.getString(4));
                     * item.setWhenId(cursor.getInt(5));
                     * item.setTriggerId(cursor.getInt(6));
                     * item.setTriggerDeviceId(7); item.setWhenDeviceId(8);
                     * item.setIconControl(cursor.getInt(9)); mLst.add(item);
                     */
                } while (cursor.moveToNext());
            }
            Log.e("------------------", "===========" + mLst.toString());
            
            return mLst;
            
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
}
